﻿using System;
using System.Data;
using System.Collections.Generic;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CourtesyCallTable
    /// </summary>
    public class CourtesyCallTable : CacheAbstraction
    {
        String sxFilterHierarki_Sales =
            @"
            and v.SALES_EMPLOYEE_NO IN
            (
                select :prmID from dual
                UNION ALL
                select EMPLOYEE_NO from slogix.p_slx_usergroup_member where p_slx_usergroup_id IN
                (
                    select 
                        p_slx_usergroup_id 
                    from 
                        slogix.p_slx_usergroup
                    start with 
                        p_slx_usergroup_id =
                        (
                            select max(a)
                            from 
                            (
                                select 
                                    decode(ishead, 'Y', p_slx_usergroup_id, -1) a
                                from 
                                    slogix.p_slx_usergroup_member 
                                where EMPLOYEE_NO = :prmID
                            )
                        ) 
                    connect by prior 
                        p_slx_usergroup_id = parent_id
                )
            )";

        String sxFilterHierarki_CAR =
            @"
            where v.CAR_EMPLOYEE_NO IN
            (  
                select :prmID from dual
                UNION ALL          
                select 
                    NIK 
                from 
                    v_icp_escalation
                /**/start with 
                    NIK = :prmID
                connect by prior 
                    t_qrtz_user_destination_id = parent_id
				-- EMERGENCY RELEASE FROM RDY
				-- ORACLE 11G DOESN'T SUPPORT CONNECT BY
				/*where nik = :prmID --'74940445'
                      and t_qrtz_user_destination_id in(
                            select v.t_qrtz_user_destination_id
                            from   v_icp_escalation v,
                                   sni.t_qrtz_user_destination w
                            where  (w.t_qrtz_user_destination_id = v.parent_id) 
							)*/
            )";
        public DataTable GetCallList(String sxStatus)
        {
            return GetCallList(sxStatus, false);
        }
        public DataTable GetCallList(String sxStatus, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}", KCache.T_COURTESY_CALL, sxStatus, ICPUser.ID);

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                OracleParameter prmStatus = new OracleParameter();
                prmStatus.ParameterName = "prmStatus";
                prmStatus.OracleDbType = OracleDbType.VarChar;
                prmStatus.Value = sxStatus;
                DataTable dt;
                if (ICPUser.IsAdmin || ICPUser.IsAdminForum)
                {
                    String sxQuery =
                    @"
                    select
                        v1.T_COURTESY_CALL_ID,
                        v1.CONTACT_PERSON_ID,
                        v1.NEW_CONTACT_PERSON_ID,
                        v1.PLANNED_DATE,
                        v1.REALIZATION_DATE,
                        v1.CALL_STATUS,
                        v1.NOTE,
                        v1.CANCELATION_REASON,
						v1.T_FILE_UPLOAD_ID,
                        v1.ORIGINAL_FILENAME,
                        v1.NAME,
                        v1.COMPANY_NAME,
                        v1.CUSTOMER_ID,
                        v2.EMPLOYEE_NO,
                        v2.EMPLOYEE_NAME CAR
                    from
                        V_ICP_COURTESY_CALL_LIST v1
                    left join
                        V_ICP_CAR v2
                    on
                        v1.CUSTOMER_ID = v2.COMPANY_ID
                    where
                        v1.CALL_STATUS = :prmStatus
                    order by
                        v1.PLANNED_DATE desc";
                    dt = dbl.GetDataTable(sxQuery, prmStatus);
                }
                else
                {
                    List<OracleParameter> lsParams = new List<OracleParameter>();
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmStatus";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = sxStatus;
                    String sxQuery =
                    @"
                    select
                        v1.T_COURTESY_CALL_ID,
                        v1.CONTACT_PERSON_ID,
                        v1.NEW_CONTACT_PERSON_ID,
                        v1.PLANNED_DATE,
                        v1.REALIZATION_DATE,
                        v1.CALL_STATUS,
                        v1.NOTE,
                        v1.CANCELATION_REASON,
						v1.T_FILE_UPLOAD_ID,
                        v1.ORIGINAL_FILENAME,
                        v1.NAME,
                        v1.COMPANY_NAME,
                        v1.CUSTOMER_ID,
                        v2.EMPLOYEE_NO,
                        v2.EMPLOYEE_NAME CAR
                    from
                        V_ICP_COURTESY_CALL_LIST v1
                    left join
                        V_ICP_CAR v2
                    on
                        v1.CUSTOMER_ID = v2.COMPANY_ID
                    join
                        V_ICP_CUSTOMER_COMPLETE v
                    on
                        v1.CUSTOMER_ID = v.CUSTOMER_ID
                    {0}
                    and
                        v1.CALL_STATUS = :prmStatus
                    order by
                        v1.PLANNED_DATE desc";

                    if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum
                        || ICPUser.RoleId == (int)ICPUserRole.CARPimpinanCAR)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
                    }
                    else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
                    }
                    else
                    {
                        sxQuery = String.Format(sxQuery, String.Empty);
                    }
                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable GetCallDetailById(int iCCID)
        {
            return GetCallDetailById(iCCID, false);
        }
        public DataTable GetCallDetailById(int iCCID, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}", KCache.T_COURTESY_CALL, iCCID.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                OracleParameter prmId = new OracleParameter();
                prmId.ParameterName = "prmId";
                prmId.OracleDbType = OracleDbType.Number;
                prmId.Value = iCCID;

                String sxQuery =
                @"
                select
                    v1.T_COURTESY_CALL_ID,
                    v1.CONTACT_PERSON_ID,
                    v1.NEW_CONTACT_PERSON_ID,
                    v1.PLANNED_DATE,
                    v1.REALIZATION_DATE,
                    v1.CALL_STATUS,
                    v1.NOTE,
                    v1.CANCELATION_REASON,
					v1.T_FILE_UPLOAD_ID,
					v1.ORIGINAL_FILENAME,
                    v1.NAME,
                    v1.COMPANY_NAME,
                    v1.CUSTOMER_ID,
                    v2.EMPLOYEE_NAME CAR,
                    v1.CREATE_BY_NAME
                from
                    V_ICP_COURTESY_CALL_LIST v1
                left join
                    V_ICP_CAR v2
                on
                    v1.CUSTOMER_ID = v2.COMPANY_ID
                where
                    v1.T_COURTESY_CALL_ID = :prmId";

                DataTable dt = dbl.GetDataTable(sxQuery, prmId);
                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable GetReminderList(bool bSeeAll)
        {
            return GetReminderList(bSeeAll, false);
        }
        public DataTable GetReminderList(bool bSeeAll, bool bForceRefresh)
        {
            String sxCacheKey = String.Empty;
            if (bSeeAll)
            {
                sxCacheKey = String.Format("{0}{1}", KCache.T_COURTESY_CALL, "SeeAll");
            }
            else
            {
                sxCacheKey = String.Format("{0}{1}", KCache.T_COURTESY_CALL, "NotSeeAll");
            }

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxFilter = String.Empty;
                if (!bSeeAll)
                {
                    sxFilter = "and ROWNUM < 6";
                }

                String sxQuery =
                @"
                select
                    T_COURTESY_CALL_ID,
                    COMPANY_NAME,
                    NAME,
                    PLANNED_DATE
                from
                    V_ICP_COURTESY_CALL_LIST
                where
                    CALL_STATUS = 'PLANNED'
                and
                    PLANNED_DATE > sysdate
                and
                    PLANNED_DATE < sysdate + 5"
                + sxFilter +
                @"order by
                    PLANNED_DATE asc";

                DataTable dt = dbl.GetDataTable(sxQuery);
                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable GetCCQuestionDetailById(int iCCID)
        {
            return GetCCQuestionDetailById(iCCID, false);
        }
        public DataTable GetCCQuestionDetailById(int iCCID, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}", KCache.T_COURTESY_CALL_QUESTION, iCCID.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                OracleParameter prmId = new OracleParameter();
                prmId.ParameterName = "prmId";
                prmId.OracleDbType = OracleDbType.Number;
                prmId.Value = iCCID;

                String sxQuery =
                @"
                select
                    t1.T_COURTESY_CALL_ID,
                    p1.CODE QUESTION,
                    t1.VALUE,
                    t1.NOTE,
                    P1.P_QUESTION_TYPE_ID,
                    P2.CODE CODE_TYPE 
                from
                    P_QUESTION_TYPE p1 
                left join 
                    T_COURTESY_CALL_QUESTION t1
                on 
                    t1.P_QUESTION_TYPE_ID = P1.P_QUESTION_TYPE_ID
                left join
                    P_INPUT_CONTROL_TYPE p2
                on
                    p1.P_INPUT_CONTROL_TYPE_ID = p2.P_INPUT_CONTROL_TYPE_ID
                where
                    t1.T_COURTESY_CALL_ID = :prmId
                and
                    p1.VALID_TO is null";

                DataTable dt = dbl.GetDataTable(sxQuery, prmId);
                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        //edited by rista
        public DataTable GetCallSummary(String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear)
        {
            return GetCallSummary(sxCARId, iStartMonth, iStartYear, iEndMonth, iEndYear, false);
        }
        /*public DataTable GetCallSummary(String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear)
        {
            return GetCallSummary(sxCARId, iStartMonth, iStartYear, iEndMonth, iEndYear, false);
        }*/
        //end of edited

        public DataTable GetCallSummary(String sxCARId,
            int iStartMonth, int iStartYear, int iEndMonth, int iEndYear, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}_{1}_{2}{3}_{4}{5}",
                KCache.CC_SUM,
                sxCARId.ToString(),
                iStartMonth.ToString(),
                iStartYear.ToString(),
                iEndMonth.ToString(),
                iEndYear.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxExtraParam = String.Empty;
                List<OracleParameter> lsParams = new List<OracleParameter>();
                if (!ICPUser.IsAdmin)
                {
                    if (!String.IsNullOrEmpty(sxCARId))
                    {
                        lsParams.Add(new OracleParameter());
                        lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                        lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                        lsParams[lsParams.Count - 1].Value = sxCARId;
                    }
                    else
                    {
                        lsParams.Add(new OracleParameter());
                        lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                        lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                        lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                    }
                    sxExtraParam =
                            @"and t1.CREATE_BY IN
                                (
                                    select :prmCARId from dual
                                    UNION ALL
                                    SELECT DISTINCT NIK
                                    FROM 
                                    v_icp_escalation
                                    START WITH NIK = :prmCARId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )";
                }
                else
                {
                    if (!String.IsNullOrEmpty(sxCARId))
                    {
                        lsParams.Add(new OracleParameter());
                        lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                        lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                        lsParams[lsParams.Count - 1].Value = sxCARId;

                        sxExtraParam =
                            @"and t1.CREATE_BY IN
                                (
                                    select :prmCARId from dual
                                    UNION ALL
                                    SELECT DISTINCT NIK
                                    FROM 
                                    v_icp_escalation
                                    START WITH NIK = :prmCARId        
                                    CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                                )";
                    }
                }
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartYear;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndYear;

                sxExtraParam +=
                   @"and 
                    (
                        to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= t1.REALIZATION_DATE
                        and
                        ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > t1.REALIZATION_DATE
                    )";
                String sxQuery = String.Format(
                    @"
                    select
                        t1.TOTAL,
                        t1.EMPLOYEE_NAME CAR,
                        v1.NAME CONTACT_PERSON,
                        v1.COMPANY_NAME,
                        v1.CUSTOMER_ID,
                        t1.CREATE_BY
                    from
                    (
                        select
                            count(t1.T_COURTESY_CALL_ID) TOTAL,
                            t1.CONTACT_PERSON_ID,
                            t1.NEW_CONTACT_PERSON_ID,
                            t1.CREATE_BY,
                            emp.EMPLOYEE_NAME
                        from
                            t_courtesy_call t1
                        join
                            slogix.employee emp
                        on
                            t1.CREATE_BY = emp.EMPLOYEE_NO
                        where
                            t1.call_status = 'REALIZED'
                        {0}
                        group by
                            t1.CONTACT_PERSON_ID,
                            t1.NEW_CONTACT_PERSON_ID,
                            t1.CREATE_BY,
                            emp.EMPLOYEE_NAME
                    ) t1
                    join 
                    (
                        SELECT   v1.CONTACT_PERSON_ID,
                                 v1.NAME,
                                 v1.IS_NEW_CP,
                                 comp.COMPANY_ID CUSTOMER_ID,
                                 comp.COMPANY_NAME
                          FROM      (SELECT   pic.SITE_PIC_ID CONTACT_PERSON_ID,
                                              pic.PIC_NAME NAME,
                                              'N' IS_NEW_CP,
                                              site.COMPANY_ID CUSTOMER_ID
                                       FROM      SNI.SITE_PIC pic
                                              LEFT JOIN
                                                 sni.site site
                                              ON pic.site_id = site.site_id
                                      WHERE   NVL (pic.VALID_TO, SYSDATE + 1) > SYSDATE
                                     UNION ALL
                                     SELECT   ncp.NEW_CONTACT_PERSON_ID,
                                              ncp.NAME,
                                              'Y' IS_NEW_CP,
                                              ncp.CUSTOMER_ID
                                       FROM   NEW_CONTACT_PERSON ncp) v1
                                 left JOIN
                                    sni.company comp
                                 ON v1.CUSTOMER_ID = comp.company_id
                    ) v1
                    on
                    (t1.CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'N')
                    or
                    (t1.NEW_CONTACT_PERSON_ID = v1.CONTACT_PERSON_ID and v1.IS_NEW_CP = 'Y')",
                     sxExtraParam);
                
                //edited by Rista
					//sxQuery = "select * from courtesy_call_temp";
                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
				    //DataTable dt = dbl.GetDataTable(sxQuery);

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.COURTESY_CALL;
        }
    }
}